const express = require('express');
const router = express.Router();
const pool = require('../config/db');

//测天区效率统计
router.get('/obsSkyNumByMinutes', async (req, res) => {  
  try {  
    const client = await pool.connect();  
    try {  
      const querySql = "WITH filtered_data AS (  \n" +
                      "    SELECT  \n" +
                      "        date_trunc('minute', gen_time) AS minTime,  \n" +
                      "        cam.name as camName,  \n" +
                      "        sky.sky_name sky_id  \n" +
                      "    FROM  \n" +
                      "        fits_file2  ff2\n" +
                      "		INNER JOIN observation_sky sky on sky.sky_id=ff2.sky_id\n" +
                      "		INNER JOIN camera cam on cam.camera_id=ff2.cam_id\n" +
                      "    WHERE  \n" +
                      "        cam_id % 5 = 0  \n" +
                      "),  \n" +
                      "grouped_data AS (  \n" +
                      "    SELECT  \n" +
                      "        minTime,  \n" +
                      "        COUNT(DISTINCT sky_id) AS sky_id_count,  \n" +
                      "        ARRAY_AGG(DISTINCT camName || '-' || sky_id ) AS sky_id_list  \n" +
                      "    FROM  \n" +
                      "        filtered_data  \n" +
                      "    GROUP BY  \n" +
                      "        minTime  \n" +
                      ")  \n" +
                      "SELECT  \n" +
                      "    minTime,  \n" +
                      "    sky_id_count,  \n" +
                      "		to_json(sky_id_list) AS sky_ids_json  \n" +
                      "FROM  \n" +
                      "    grouped_data  \n" +
                      "ORDER BY  \n" +
                      "    minTime;";
      const result = await client.query(querySql);  
      res.json(result.rows); // 将结果以 JSON 格式返回  
    } finally {  
      client.release();  
    }  
  } catch (err) {  
    console.error('Error executing query', err);  
    res.status(500).json({ error: 'Internal Server Error' });  
  }  
});  
//GWAC观测天区效率统计
router.get('/obsSkyNumByMinutesHis', async (req, res) => {  
  try {  
    const client = await pool.connect();  
    try {  
      const { startDate, endDate } = req.query;
      
      if (!startDate || !endDate) {
        return res.status(400).json({ error: 'Missing required parameters: startDate, endDate' });
      }
      
      const querySql = `
        WITH filtered_data AS (
            SELECT  
                date_trunc('minute', gen_time) AS minTime,  
                cam.name AS camName,  
                sky.sky_name AS sky_id  
            FROM  
                fits_file2_his ff2
                INNER JOIN observation_sky sky ON sky.sky_id = ff2.sky_id
                INNER JOIN camera cam ON cam.camera_id = ff2.cam_id
            WHERE  
                cam_id % 5 = 0 AND  
                gen_time BETWEEN $1 AND $2
        ),  
        grouped_data AS (
            SELECT  
                minTime,  
                COUNT(DISTINCT sky_id) AS sky_id_count,  
                ARRAY_AGG(DISTINCT camName || '-' || sky_id) AS sky_id_list  
            FROM  
                filtered_data  
            GROUP BY  
                minTime  
        )  
        SELECT  
            minTime,  
            sky_id_count,  
            to_json(sky_id_list) AS sky_ids_json  
        FROM  
            grouped_data  
        ORDER BY  
            minTime;`;
      
      const result = await client.query(querySql, [startDate, endDate]);  
      res.json(result.rows);  
    } finally {  
      client.release();  
    }  
  } catch (err) {  
    console.error('Error executing query', err);  
    res.status(500).json({ error: 'Internal Server Error' });  
  }  
});
module.exports = router;